﻿Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading

Public Class frmrpt_voyagedamage
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Public Shared idvoyage As Integer
    Dim pathExcel As String

    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Private Sub frmrpt_voyagedamage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()

        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from voyage;"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                With ListView1.Items.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("N")
                    .SubItems.Add(mySqlReader("VOYVESNAMEN"))
                    .SubItems.Add(mySqlReader("VOYDATESN"))
                    .SubItems.Add(mySqlReader("VOYDATEEN"))
                    .SubItems.Add(Format(mySqlReader("VOYAGEID"), "000"))
                    .SubItems.Add("S")
                    .SubItems.Add(mySqlReader("VOYVESNAMES"))
                    .SubItems.Add(mySqlReader("VOYDATESS"))
                    .SubItems.Add(mySqlReader("VOYDATEES"))
                End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()
    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListView1.Click
        idvoyage = ListView1.SelectedItems(0).SubItems(0).Text
    End Sub

    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        If ListView1.SelectedItems.Count > 0 Then
            FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
            FolderBrowserDialog1.ShowNewFolderButton = True
            FolderBrowserDialog1.SelectedPath = "C:\"
            If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Try
                    Dim t = New Thread(New ThreadStart(AddressOf excelReport))
                    t.Start()
                    CircularProgress1.IsRunning = True
                Catch ex As Exception

                End Try
            End If

        Else
            MsgBox("กรุณาเลือก Voyage")
        End If

    End Sub
    Private Sub excelReport()
        Dim count_Row As Integer = 5
        Dim count_Row2 As Integer = 9

        pathExcel = FolderBrowserDialog1.SelectedPath

        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        excelsheets.Rows("3:3").rowheight = 20


        With (excelsheets)
            .Range("A1:Q900").Font.Name = "Angsana New"

            .Range("A2:Q900").Font.Size = 14
            Dim CheckIndex As Integer
            Dim i As Integer
            Dim CheckData As Boolean
            CheckData = False
            CheckIndex = ListView1.Items.Count
            Dim J As Integer
            For J = 7 To 10
                .Range("A4").Borders(J).Weight = 2 ' xlThin
                .Range("B4").Borders(J).Weight = 2 ' xlThin
                .Range("C4").Borders(J).Weight = 2 ' xlThin
                .Range("D4").Borders(J).Weight = 2 ' xlThin
                .Range("E4").Borders(J).Weight = 2 ' xlThin
                .Range("F4").Borders(J).Weight = 2 ' xlThin
                .Range("G4").Borders(J).Weight = 2 ' xlThin
                .Range("H4").Borders(J).Weight = 2 ' xlThin


            Next
            With .Range("A2:F2")
                .Merge()


                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "YKP PORT CONTAINER DAMAGE VOYAGE" + Format(idvoyage, "000").ToString + "N"
                .Font.Size = 16
                ''.ColumnWidth = 20
            End With
            With .Range("A3:F3")
                .Merge()
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "จำนวนตู้รอตรวจสอบ"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("A4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Container No."
                .ColumnWidth = 15
            End With
            With .Range("B4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Agent Line"
                .ColumnWidth = 12
            End With

            With .Range("C4")


                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Date Recieve"
                .Font.Size = 14
                .ColumnWidth = 14
            End With
            With .Range("D4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "Size"
                .Font.Size = 14
                ''.ColumnWidth = 12
            End With
            With .Range("E4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "VOYN"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("F4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ใช้งานได้"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("G4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ทำความสะอาด"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            With .Range("H4")

                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                .Font.Bold = True
                .Value = "ซ่่อม"
                .Font.Size = 14
                ''.ColumnWidth = 20
            End With
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select  * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID  where CTNSTAT= '0'and VOYAGEID ='" & idvoyage & "' order by CTNMAINID ASC;"
            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand

            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())

                    With .Range("A" + count_Row.ToString)
                        .Value = mySqlReader("CTNSTRING")
                    End With

                    With .Range("B" + count_Row.ToString)
                        .Value = mySqlReader("CTNAGENT")
                    End With
                    With .Range("C" + count_Row.ToString)
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
                        .Value = mySqlReader("VOYDATEEN")
                    End With
                    With .Range("D" + count_Row.ToString)
                        .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
                        .Value = mySqlReader("CTNSIZE")
                    End With
                    With .Range("E" + count_Row.ToString)
                        .Value = Format(mySqlReader("VOYAGEID"), "000") + "N"
                    End With
                    count_Row += 1
                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try


            For i = 5 To count_Row
                .Range("B" & i.ToString() & ":B" & i.ToString()).Borders(7).Weight = 2
                .Range("A" & i.ToString() & ":A" & i.ToString()).Borders(7).Weight = 2
                .Range("C" & i.ToString() & ":C" & i.ToString()).Borders(7).Weight = 2
                .Range("D" & i.ToString() & ":D" & i.ToString()).Borders(7).Weight = 2
                .Range("E" & i.ToString() & ":E" & i.ToString()).Borders(7).Weight = 2
                .Range("F" & i.ToString() & ":F" & i.ToString()).Borders(7).Weight = 2
                .Range("G" & i.ToString() & ":G" & i.ToString()).Borders(7).Weight = 2
                .Range("H" & i.ToString() & ":H" & i.ToString()).Borders(7).Weight = 2
                .Range("I" & i.ToString() & ":I" & i.ToString()).Borders(7).Weight = 2
            Next
            count_Row += 1
            With .Range("A" + count_Row.ToString + ":H" + count_Row.ToString)
                .Borders(8).Weight = 2
            End With

        End With


        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportContainerDamage" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")
            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub
    Private Sub showResult(ByVal Num As Integer)
        If Label10.InvokeRequired Then
            Dim dlg As New DelegateSub(AddressOf showResult)
            Me.Invoke(dlg, Num)

        Else
            CircularProgress1.IsRunning = False

        End If
    End Sub
End Class